/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Clases;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import javax.swing.JOptionPane;

/**
 *
 * @author Ariel
 */
public class DAO {
    private Connection conexion;
    //se carga el driver de la conexión
    private void cargarDriverDerby() {
    cargarDriver("org.apache.derby.jdbc.EmbeddedDriver");
    }

    //Aqui cargamos la libreria
    private void cargarDriver(String driver) {
     try {
      Class.forName(driver);
     } catch (ClassNotFoundException ex) {
      JOptionPane.showMessageDialog(null, "No se cargar el controlador de conexion\n" + driver + "\n Codigo de error: " + ex.getMessage(),   "Error fatal", JOptionPane.ERROR_MESSAGE);
      System.exit(0);
     }
    }
    static private Connection getConnection() {
        try {
            String url = "jdbc:derby:TPIBaseDato";
            String driver = "org.apache.derby.jdbc.EmbeddedDriver";


            Class.forName(driver);
            Connection connection = DriverManager.getConnection(url);

            return connection;
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }
    }
    //Aqui se le envian el usuario de la base de datos, la clable y el nombre de la base de datos
    public DAO() {
     cargarDriverDerby();
    }
    
    public void InsertarPalabra(String p, int f, int id_doc) {
    int id_voc=0;
    try {

            Connection connection = getConnection();

            
            String sql = "Select idvoc,frecuencia From Vocabulario Where palabra=?"; //"INSERT INTO Vocabulario (palabra,frecuencia) VALUES (?,?)";

            PreparedStatement preparedStatement = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
//connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

            preparedStatement.setString(1,p);
            
            ResultSet rs = preparedStatement.executeQuery();
            if(rs != null && rs.next()){
                int fre = rs.getInt("frecuencia") + f;
                id_voc = rs.getInt("idvoc");
                rs.updateInt("frecuencia", fre );
                rs.updateRow();
            }else{
                sql = "INSERT INTO Vocabulario (palabra,frecuencia) VALUES (?,?)";
                preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                preparedStatement.setString(1,p);
                preparedStatement.setInt(2, f);
                preparedStatement.executeUpdate();
                rs = preparedStatement.getGeneratedKeys();
                if (rs != null && rs.next()) id_voc = rs.getInt(1);
            }
            
            connection.close();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
    this.InsertarVocXDoc(id_voc, id_doc);
}

    public int InsertarDoc(String nom, String dir) {
    int doc_id=0;
        try {

            Connection connection = getConnection();


            String sql = "INSERT INTO Documento (nombre,dir) VALUES (?,?)";

            PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            preparedStatement.setString(1,nom);
            preparedStatement.setString(2, dir);
            preparedStatement.executeUpdate();
            ResultSet rs = preparedStatement.getGeneratedKeys();
            
            if (rs != null && rs.next()) doc_id = rs.getInt(1);
            
            connection.close();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return doc_id;
}
    public void InsertarVocXDoc(int voc, int doc) {
    try {

            Connection connection = getConnection();


            String sql = "INSERT INTO VocabularioXDocumento (voc_id,doc_id) VALUES (?,?)";

            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1,voc);
            preparedStatement.setInt(2, doc);


            preparedStatement.executeUpdate();

            connection.close();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
}
}
